<?php
namespace app\api\controller;

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use think\facade\Cache;
use think\facade\Request;
use PhpOffice\PhpSpreadsheet\IOFactory;


class Excel
{
    // 导入Excel文件并返回数据
    public function import()
    {
        $file = Request::file('file');
        $sessionKey = Request::post('sessionKey');
        if (!$file) {
            return json(['code' => 400, 'msg' => '请上传文件']);
        }

        try {
            $spreadsheet = IOFactory::load($file->getRealPath());
            $sheet = $spreadsheet->getActiveSheet();
            $data = $sheet->toArray();

            // 提取表头并验证格式
            $headers = array_shift($data);
            if ($headers !== ['序号','姓名', '工资', '养老', '医疗', '工伤', '失业', '公积金']) {
                return json(['code' => 400, 'msg' => 'Excel格式不正确']);
            }

            // 转换数据格式
            $result = [];
            foreach ($data as $row) {
                $result[] = [
                    'id'            => $row[0],
                    'name'          => $row[1],
                    'salary'        => $row[2],
                    'pension'       => $row[3],
                    'medical'       => $row[4],
                    'injury'        => $row[5],
                    'unemployment'  => $row[6],
                    'housing_fund'  => $row[7]
                ];
            }
            
            Cache::store('redis')->set($sessionKey,json_encode($result, JSON_UNESCAPED_UNICODE));
            return json(['code' => 200, 'data' => $result]);

        } catch (\Exception $e) {
            return json(['code' => 500, 'msg' => '文件处理失败: ' . $e->getMessage()]);
        }
    }

    // 整合数据
    public function merge()
    {
        $serializedfirstData = Cache::store('redis')->get('first_data');
        $serializedsecondData = Cache::store('redis')->get('second_data');
        Cache::delete('first_data');
        Cache::delete('second_data');
        $firstData = json_decode($serializedfirstData, true);
        $secondData = json_decode($serializedsecondData, true);
        $merged = [];

        foreach (array_merge($firstData, $secondData) as $item) {
            $name = $item['name'];
            if (!isset($merged[$name])) {
                $merged[$name] = $item;
                continue;
            }

            // 数值字段累加
            $merged[$name]['salary'] += $item['salary'];
            $merged[$name]['pension'] += $item['pension'];
            $merged[$name]['medical'] += $item['medical'];
            $merged[$name]['injury'] += $item['injury'];
            $merged[$name]['unemployment'] += $item['unemployment'];
            $merged[$name]['housing_fund'] += $item['housing_fund'];
        }
        Cache::store('redis')->set('merged_data',json_encode($merged, JSON_UNESCAPED_UNICODE));
        return json(['code' => 200, 'data' => array_values($merged)]);
    }

    // 导出Excel文件
    public function exportExcel()
    {
        $serializedmergedData = Cache::store('redis')->get('merged_data');
        Cache::delete('merged_data');
        $data = json_decode($serializedmergedData, true);
        if (empty($data)) {
            return json(['code' => 404, 'msg' => '没有可导出的数据']);
        }

        try {
            $spreadsheet = new Spreadsheet();
            $sheet = $spreadsheet->getActiveSheet();

            // 设置表头
            $sheet->fromArray([
                ['姓名', '工资', '养老', '医疗', '工伤', '失业', '公积金']
            ], null, 'A1');

            // 填充数据
            $row = 2;
            foreach ($data as $item) {
                $sheet->fromArray([
                    $item['name'],
                    $item['salary'],
                    $item['pension'],
                    $item['medical'],
                    $item['injury'],
                    $item['unemployment'],
                    $item['housing_fund']
                ], null, "A{$row}");
                $row++;
            }

            // 输出文件
            $writer = new Xlsx($spreadsheet);
            $filename = '整合数据_' . date('YmdHis') . '.xlsx';

            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $filename . '"');
            header('Cache-Control: max-age=0');

            $writer->save('php://output');
            exit;

        } catch (\Exception $e) {
            return json(['code' => 500, 'msg' => '导出失败: ' . $e->getMessage()]);
        }
    }
}
